package com.ruoyi.business.controller;

import com.alibaba.fastjson.JSONObject;
import com.ruoyi.business.entity.external.Stat;
import com.ruoyi.common.annotation.Log;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.core.domain.model.LoginUser;
import com.ruoyi.common.core.page.TableSupport;
import com.ruoyi.common.enums.BusinessType;
import com.ruoyi.common.utils.SecurityUtils;
import com.ruoyi.common.utils.ServletUtils;
import com.ruoyi.common.utils.StringUtils;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.system.domain.SysCommonDataProperty;
import com.ruoyi.system.domain.vo.R;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

@RestController
@RequestMapping("statisticsExternal")
public class StatisticsExternalController {

    @Autowired
    @Qualifier("juheJdbcTemplate123")
    private JdbcTemplate jdbcTemplate123;

    @RequestMapping("/list")
    public R list(Stat stat) {

        LoginUser loginUser = SecurityUtils.getLoginUser();
        String username = loginUser.getUsername();
        String mediaId = username.replace("stat_", "");
        if ("admin".equals(username)) {
            mediaId = null;
        }

        Integer pageNum = ServletUtils.getParameterToInt(TableSupport.PAGE_NUM);
        Integer pageSize = ServletUtils.getParameterToInt(TableSupport.PAGE_SIZE);

        String sql = "select * from stat where 1=1";
        String sqlCount = "select count(*) from stat where 1=1";

        List<Object> params = new ArrayList<>();
        List<Object> paramsCount = new ArrayList<>();

        if (mediaId != null) {
            sql += " and media_id = ?";
            sqlCount += " and media_id = ?";
            params.add(mediaId);
            paramsCount.add(mediaId);
        }


        if (StringUtils.isNotBlank(stat.getTime())) {
            sql += " and time = ?";
            sqlCount += " and time = ?";
            params.add(stat.getTime());
            paramsCount.add(stat.getTime());
        }

        if (StringUtils.isNotBlank(stat.getAds_id())) {
            sql += " and ads_id = ?";
            sqlCount += " and ads_id = ?";
            params.add(stat.getAds_id());
            paramsCount.add(stat.getAds_id());
        }

        if (StringUtils.isNotBlank(stat.getDsp_id())) {
            sql += " and dsp_id = ?";
            sqlCount += " and dsp_id = ?";
            params.add(stat.getDsp_id());
            paramsCount.add(stat.getDsp_id());
        }

        sql += " order by uptime desc limit ?,?";
        params.add((pageNum - 1) * pageSize);
        params.add(pageSize);
        List<Map<String, Object>> list = jdbcTemplate123.queryForList(sql, params.toArray(new Object[]{}));
        List<Stat> resList = new ArrayList<>();
        list.forEach(item -> {
            //点击率。 保留两位小数
            String show = item.get("show") + "";
            String click = item.get("click") + "";
            if (item.get("show") != null && item.get("click") != null && !"0".equals(click)) {
                item.put("click_rate", String.format("%.2f", Double.parseDouble(item.get("click").toString()) / Double.parseDouble(item.get("show").toString()) * 100));
            } else {
                item.put("click_rate", 0);
            }
            //cpc 和 cpm都保留两位小数
            if (item.get("cost") != null && item.get("click") != null && !"0".equals(click)) {
                item.put("cpm", String.format("%.2f", Double.parseDouble(item.get("cost").toString()) / 100 / Double.parseDouble(item.get("click").toString()) * 1000));
                item.put("cpc", String.format("%.2f", Double.parseDouble(item.get("cost").toString()) / 100 / Double.parseDouble(item.get("click").toString())));
            } else {
                item.put("cpm", 0);
                item.put("cpc", 0);
            }
            //item转为Stat实体
            Stat temp = JSONObject.parseObject(JSONObject.toJSONString(item), Stat.class);
            resList.add(temp);
        });


        int count = jdbcTemplate123.queryForObject(sqlCount, paramsCount.toArray(new Object[]{}), Integer.class);
        return R.success().data("data", resList).data("count", count);
    }

    @RequestMapping("/chartData")
    public R chartData(Stat stat) {

        String sql = "SELECT\n" +
                "\tDATE_FORMAT( `hour`, '%H' ) AS hour_part,\n" +
                "\tSUM( `show` ) AS show_sum,\n" +
                "\tSUM( `click` ) AS click_sum,\n" +
                "\tSUM( req ) AS req_sum,\n" +
                "\tSUM( bid ) AS bid_sum,\n" +
                "\tSUM( cost ) AS cost_sum,\n" +
                "\tSUM( revenue ) AS revenue_sum,\n" +
                "\tSUM( margin ) AS margin_sum \n" +
                "FROM\n" +
                "\tstat \n" +
                "WHERE 1 = 1 ";

        List<Object> params = new ArrayList<>();
        if (StringUtils.isNotBlank(stat.getTime())) {
            sql += " and time = ?";
            params.add(stat.getTime());
        }

        if (StringUtils.isNotBlank(stat.getAds_id())) {
            sql += " and ads_id = ?";
            params.add(stat.getAds_id());
        }

        if (StringUtils.isNotBlank(stat.getDsp_id())) {
            sql += " and dsp_id = ?";
            params.add(stat.getDsp_id());
        }

        sql += " GROUP BY DATE_FORMAT( `hour`, '%H' ) ORDER BY DATE_FORMAT( `hour`, '%H' );";

        List<Map<String, Object>> list = jdbcTemplate123.queryForList(sql, params.toArray(new Object[]{}));
        return R.success().data("data", list);
    }

    /**
     * 导出
     */
    @GetMapping("/exportData")
    public AjaxResult exportData(Stat stat){
        R res = this.list(stat);
        Map<String, Object> data = res.getData();
        List<Stat> list = (List<Stat>) data.get("data");
        ExcelUtil<Stat> util = new ExcelUtil<Stat>(Stat.class);
        return util.exportExcel(list, "数据统计");
    }

}
